import pandas as pd
import datetime
import jsjj.excel2Mysql.pub as pub

# 统一表名称的入口
tableName = '拼多多_电霸_近30天热销商品'


def clear(conn, engine):
    sql = 'delete from a                                                                            '
    sql += '    using ' + tableName + ' as a, ' + tableName + ' as b                                         '
    sql += '    where (a.id < b.id)                                                                  '
    sql += '    and (a.goodsId = b.goodsId and a.statDate = b.statDate and a.catLeaf = b.catLeaf)  '

    # 做基础的数据排重
    # engine.execute(sql)
    return


def mainProcess(conn, engine):
    filelist = pub.getRawFileList(getDirectory())

    blackList = pd.read_csv('./excludeFiles')

    # 拼多多-电霸-热销商品
    for file in filelist[0]:

        if file.find('~') != -1:
            # 临时文件不处理
            continue

        if file.find('拼多多-TOP100单品-电脑桌-5月.xlsx') != -1:
            debug = ''
        if blackList[blackList['excludeFileName'].isin([file])].shape[0] > 0:
            # 黑名单中的暂时不处理
            print('跳过了文件:' + file)
            continue

        # 读取
        df = get(file)

        # 转换
        dfMysql = transfer(df, file)

        # 存盘
        # 解决可能出现的超时问题bugfix 2020.08.18
        conn.connection.connection.ping(reconnect=True)
        dfMysql.to_sql(name=getTableName(), con=conn, if_exists='append', index=False)

    # 初步清洗
    clear(conn, engine)

    return


def getDirectory():
    fileDirectory = r'D:\简尚家居\钉钉下载目录\拼多多19个类目7月Top100'
    return fileDirectory


def getTableName():
    mysqlTableName = tableName
    return mysqlTableName


def get(file):
    return pd.read_excel(file, sheet_name=0)


def transfer(df, fileName):
    print('本次处理文件为:' + fileName)

    columnsMysql = ['statDate', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10',
                    'col11']
    dfMysql = pd.DataFrame(
        df.loc[5:, ['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6',
                    'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10', 'Unnamed: 11',
                    'Unnamed: 12']])

    # 6月是不需要存储的
    # dfMysql.columns.insert(1, 'statDate')
    dfMysql.rename(
        columns={'Unnamed: 1': 'statDate', 'Unnamed: 2': 'cat1', 'Unnamed: 3': 'cat2', 'Unnamed: 4': 'catLeaf',
                 'Unnamed: 6': 'goodsName', 'Unnamed: 7': 'goodsId', 'Unnamed: 8': 'sales1',
                 'Unnamed: 9': 'sales7', 'Unnamed: 10': 'sales30', 'Unnamed: 11': 'increment7',
                 'Unnamed: 12': 'groupBuyPrice'}, inplace=True)
    dfMysql = dfMysql.drop(columns=['Unnamed: 5'])
    dfMysql['statDate'] = datetime.datetime.strptime(df.iat[2, 0].split('：')[1], '%Y.%m.%d')
    dfMysql['picUrl'] = ''
    dfMysql['运行模式'] = '月模式'
    dfMysql['统计月'] = '7月'
    dfMysql['统计周'] = ''
    dfMysql['统计日'] = ''
    dfMysql['统计日周月'] = ''
    dfMysql['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    return dfMysql
